package com.maxd.respository.userinfo;

import com.maxd.model.UserInfo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.Repository;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.util.List;

public interface UserRepository extends Repository<UserInfo, Long> {

    @Query(value = "select count(*) from user_info where create_at like concat('%',:time,'%') and platform=:platform",nativeQuery = true)
    int getUserPlatformByTime(@Param("time") String time,@Param("platform") String platform);


    @Query(value = "from UserInfo s where s.phone=:phone order by id desc")
    Page<UserInfo> find(@Param("phone") String phone, Pageable pageable);

    @Query(value = "select s.createAt,s.image_url,s.nickName,s.relationId,s.balance from UserInfo s where s.invitation=:invitation order by id desc")
    Page<UserInfo> getUsersByInvitation(@Param("invitation") String invitation, Pageable pageable);

    //直属用户id列表
    @Query(value = "select s.id from UserInfo s where s.invitation=:relationId order by id desc")
    List<Long> getUsersByRelationId(@Param("relationId") String relationId);

    //非直属用户id列表
    @Query(value = "select s.id from user_info s where invitation in (select relation_id from user_info u where u.invitation=:relationId)",nativeQuery = true)
    List<Long> getUsersByRelationIds(@Param("relationId") String relationId);

    @Query(value = "from UserInfo s where s.invitation=:invitation and s.balance=0")
    List<UserInfo> getInvitation(@Param("invitation") String invitation);

    @Query(value = "from UserInfo s where s.invitation=:invitation")
    Page<UserInfo> getInvitations(@Param("invitation") String invitation,Pageable pageable);

    @Query(value = "select * from user_info where invitation in (select relation_id from user_info where invitation=:invitation and length(relation_id)>0 and relation_id is not null)",nativeQuery = true)
    Page<UserInfo> getInvitationss(@Param("invitation") String invitation,Pageable pageable);

    @Query(value = "select count(t.id) from UserInfo t where t.invitation=:invitation")
    String getCount(@Param("invitation") String invitation);


    @Query(value = "select count(t.id) from UserInfo t where t.invitation=:invitation and t.balance=0")
    String getCounts(@Param("invitation") String invitation);

    @Query(value = "select count(t.id) from UserInfo t")
    String getCountNumber();

    @Query(value = "select count(t.id) from UserInfo t where t.isInvitation=:isInvitation")
    String getCountNumberMember(@Param("isInvitation") int isInvitation);

    @Query(value = "select count(s.id) from UserInfo s where s.isInvitation=:isInvitation and s.createAt like concat('%',:time,'%') ")
    String getCountNumberByTime(@Param("time") String time, @Param("isInvitation") int isInvitation);

    @Query(value = "select count(s.id) from UserInfo s where s.createAt like concat('%',:time,'%') ")
    String getCountNumberByTime(@Param("time") String time);

    @Query(value = "select count(s.id) from UserInfo s where s.relationId is not null and s.createAt like concat('%',:time,'%') ")
    String getCountNumberByTimeByRelationId(@Param("time") String time);

    @Query(value = "select count(s.id) from UserInfo s where s.relationId is null and s.createAt like concat('%',:time,'%') ")
    String getCountNumberByTimeRAndRelationIdIsNull(@Param("time") String time);

    @Query(value = "select count(s.id) from UserInfo s where s.phone is not null and s.createAt like concat('%',:time,'%') ")
    String getCountNumberByTimeByPhone(@Param("time") String time);

    @Query(value = "select count(s.id) from UserInfo s where s.openId is not null and s.createAt like concat('%',:time,'%') ")
    String getCountNumberByTimeByWx(@Param("time") String time);

    @Query(value = "select count(s.id) from UserInfo s where s.mpId is not null and s.createAt like concat('%',:time,'%') ")
    String getCountNumberByTimeByMp(@Param("time") String time);


    @Query(value = "select count(s.id) from UserInfo s where s.platform=:platform and s.createAt like concat('%',:time,'%') ")
    String getCountNumberByTimeAndPlatform(@Param("time") String time, @Param("platform") String platform);

    @Query(value = "select count(s.id) from UserInfo s where s.platform=:platform and s.createAt like concat('%',:createAt,'%')")
    String getCountUser(@Param("platform") String platform, @Param("createAt") String createAt);

    @Query(value = "from UserInfo s where s.phone=:userName")
    UserInfo login(@Param("userName") String userName);

    @Query(value = "from UserInfo s where s.id=:id")
    UserInfo getUserById(@Param("id") Long id);

    @Query(value = "from UserInfo s where s.phone=:phone")
    UserInfo getUserByPhone(@Param("phone") String phone);

    @Query(value = "from UserInfo s where s.phone=:phone order by createAt desc")
    List<UserInfo> queryByPhone(@Param("phone") String phone);

    @Query(value = "from UserInfo s where s.pdd=:pdd")
    UserInfo getUserByPdd(@Param("pdd") String pdd);

    @Query(value = "from UserInfo s where s.jd=:jd")
    UserInfo getUserByJd(@Param("jd") String jd);

    @Query(value = "from UserInfo s where s.openId=:openId")
    UserInfo getUserByWxId(@Param("openId") String openId);

    @Query(value = "from UserInfo s where s.unionid=:unionid")
    UserInfo getUserByUnionid(@Param("unionid") String unionid);

    @Query(value = "from UserInfo s where s.open_uid=:open_uid")
    UserInfo getUserByOpen_uid(@Param("open_uid") String open_uid);

    @Query(value = "from UserInfo s where s.tb_openid=:tb_openid")
    UserInfo getUserByTbOpenid(@Param("tb_openid") String tb_openid);

    @Query(value = "from UserInfo s where s.mpId=:mpId")
    UserInfo getUserByMpId(@Param("mpId") String mpId);


    @Query(value = "from UserInfo s where s.relationId=:relationId")
    UserInfo getUserByRelationId(@Param("relationId") String relationId);

    @Query(value = "from UserInfo s where s.invitationCode=:invitationCode")
    UserInfo getUserByInvitationCode(@Param("invitationCode") String invitationCode);

    @Query(value = "from UserInfo s where s.relationId<>null")
    List<UserInfo> findByRelationIdNotNull();

    @Query(value = "select s.invitation from UserInfo s group by s.invitation")
    List<String> findRelationList();

    @Modifying
    @Query(value = "update UserInfo s set s.state=:state where s.id=:id")
    Integer updateState(@Param("state") String state, @Param("id") long id);

    @Modifying
    @Query(value = "update UserInfo s set s.pwd=:pwd where s.id=:id")
    Integer updatePwd(@Param("pwd") String pwd, @Param("id") String id);

    @Query(value = "select s.invitation from UserInfo s where s.createAt like concat('%',:time,'%') group by s.invitation")
    List<Long> findInvitationList(@Param("time") String time);

    @Query(value = "select count(t.id) from UserInfo t where t.invitation=:invitation and t.createAt like concat('%',:time,'%')")
    Long getCountByTime(@Param("invitation") String invitation,@Param("time") String time);

    @Modifying
    @Transactional
    @Query(value = "update UserInfo s set s.jifen=s.jifen+:jifen where s.id=:id")
    Integer updateJiFenAdd(@Param("jifen") String jifen,@Param("id") Long id);

    @Modifying
    @Transactional
    @Query(value = "update UserInfo s set s.jifen=s.jifen-:jifen where s.id=:id")
    Integer updateJiFenDelete(@Param("jifen") String jifen,@Param("id") Long id);

    @Modifying
    @Transactional
    @Query(value = "update UserInfo s set s.promotePointTotal = IFNULL(s.promotePointTotal, 0) + :point, s.promotePointAvailable = IFNULL(s.promotePointAvailable, 0) + :point where s.id=:id")
    Integer addPromotePoint(@Param("point") BigDecimal point, @Param("id") Long id);

    @Modifying
    @Transactional
    @Query(value = "update UserInfo s set s.promotePointAvailable = IFNULL(s.promotePointAvailable, 0) - :point, s.promotePointUsed = IFNULL(s.promotePointUsed, 0) + :point where s.id=:id")
    Integer subPromotePoint(@Param("point") BigDecimal point, @Param("id") Long id);

    @Modifying
    @Transactional
    @Query(value = "update UserInfo s set s.pointTotal = IFNULL(s.pointTotal, 0) + :point, s.pointAvailable = IFNULL(s.pointAvailable, 0) + :point where s.id=:id")
    Integer addPoint(@Param("point") BigDecimal point, @Param("id") Long id);

    @Modifying
    @Transactional
    @Query(value = "update UserInfo s set s.pointAvailable = IFNULL(s.pointAvailable, 0) - :point, s.pointUsed = IFNULL(s.pointUsed, 0) + :point where s.id=:id")
    Integer subPoint(@Param("point") BigDecimal point, @Param("id") Long id);

    @Modifying
    @Transactional
    @Query(value = "update UserInfo s set s.orderJifen=:orderJifen where s.id=:id")
    Integer updateOrderJifen(@Param("orderJifen") String orderJifen,@Param("id") Long id);

    @Query(value = "from UserInfo")
    List<UserInfo> selectUserList();

    //积分抽奖：加分
    @Modifying
    @Transactional
    @Query(value = "update UserInfo s set s.orderJifen=s.orderJifen+:orderJifen where s.id=:id")
    Integer addOrderJifen(@Param("orderJifen") String orderJifen,@Param("id") Long id);
    //积分抽奖：减分
    @Modifying
    @Transactional
    @Query(value = "update UserInfo s set s.orderJifen=s.orderJifen-:orderJifen where s.id=:id")
    Integer lessOrderJifen(@Param("orderJifen") String orderJifen,@Param("id") Long id);

    @Modifying
    @Transactional
    @Query(value = "update UserInfo s set s.nickName=:nickName where s.id=:userId")
    Integer updateNickName(@Param("nickName") String nickName,@Param("userId") Long userId);

    @Modifying
    @Transactional
    @Query(value = "update UserInfo s set s.image_url=:imageUrl where s.id=:userId")
    Integer updateImgUrl(@Param("imageUrl") String imageUrl,@Param("userId") Long userId);

}
